using MediatR;
using System.Data;
using Dapper;
using RBAC.Read.Api.Application.Dto.ERP.SalesManagement.H;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.IO;
using RBAC.Read.Api.Application.Command.ERP.SalesManagement.H;
using RBAC.Interstructrue.Base;
using Microsoft.Extensions.Logging;
using RBAC.Read.Api.Utils;

namespace RBAC.Read.Api.Application.Handler.ERP.SalesManagement.H
{
    /// <summary>
    /// 导出发货通知单处理器
    /// </summary>
    public class ExportDeliveryNoticeCommandHandler : IRequestHandler<ExportDeliveryNoticeCommand, byte[]>
    {
        private readonly IDbConnectionFactory _dbConnectionFactory;
        private readonly ILogger<ExportDeliveryNoticeCommandHandler> _logger;

        public ExportDeliveryNoticeCommandHandler(IDbConnectionFactory dbConnectionFactory, ILogger<ExportDeliveryNoticeCommandHandler> logger)
        {
            _dbConnectionFactory = dbConnectionFactory;
            _logger = logger;
        }

        public async Task<byte[]> Handle(ExportDeliveryNoticeCommand request, CancellationToken cancellationToken)
        {
            IDbConnection connection = null;
            try
            {
                // 使用重试机制创建数据库连接
                connection = _dbConnectionFactory.CreateConnectionWithRetry(maxRetries: 3, delayMs: 2000);
                
                // 验证连接状态
                if (connection.State != ConnectionState.Open)
                {
                    _logger.LogError("Database connection is not open after retry attempts");
                    throw new InvalidOperationException("Unable to establish database connection");
                }
                
                _logger.LogInformation("Database connection opened successfully for export operation");
                
                // 设置命令超时
                var commandTimeout = 300; // 5分钟命令超时
                
                // 构建基础SQL查询
                var sql = @"
            SELECT
                dn.Bill_code as BillCode,
                dn.Sales_order_code as SalesOrderCode,
                c.CustomerName,
                u.UserName as SalesmanName,
                w.WarehouseName,
                dn.Receiver,
                dn.Phone,
                dn.Address,
                dn.Biz_type as BizType,
                CASE
                    WHEN dn.Status = 0 THEN '开立'
                    WHEN dn.Status = 1 THEN '审核'
                    WHEN dn.Status = 2 THEN '退回'
                    ELSE '未知'
                END as StatusDescription,
                dni.Material_code as MaterialCode,
                dni.Material_name as MaterialName,
                dni.Qty as DeliveryQuantity,
                0 as OutboundQuantity,
                m.ModelSpec as PackagingSpecification,
                dni.Package_qty as PieceCount,
                dn.Bill_date as BillDate,
                dn.Delivery_date as DeliveryDate,
                dni.Batch_no as BatchNo,
                dn.Remark
            FROM Delivery_notice dn
            LEFT JOIN Customer c ON dn.Customer_id = c.Id AND c.IsDeleted = 0
            LEFT JOIN Delivery_notice_item dni ON dn.Id = dni.Delivery_notice_id AND dni.IsDeleted = 0
            LEFT JOIN Mater m ON dni.Material_id = m.Id AND m.IsDeleted = 0
            LEFT JOIN User u ON dn.Salesman_id = u.Id AND u.IsDeleted = 0
            LEFT JOIN WarehouseList w ON dn.Warehouse_id = w.Id AND w.IsDeleted = 0
            WHERE dn.IsDeleted = 0";

                // 构建动态查询条件
                var parameters = new DynamicParameters();
                
                if (!string.IsNullOrWhiteSpace(request.SalesOrderCode))
                {
                    sql += " AND dn.Sales_order_code LIKE @SalesOrderCode";
                    parameters.Add("@SalesOrderCode", $"%{request.SalesOrderCode.Trim()}%");
                }

                if (!string.IsNullOrWhiteSpace(request.BillCode))
                {
                    sql += " AND dn.Bill_code LIKE @BillCode";
                    parameters.Add("@BillCode", $"%{request.BillCode.Trim()}%");
                }

                if (!string.IsNullOrWhiteSpace(request.CustomerName))
                {
                    sql += " AND c.CustomerName LIKE @CustomerName";
                    parameters.Add("@CustomerName", $"%{request.CustomerName.Trim()}%");
                }

                if (!string.IsNullOrWhiteSpace(request.SalesmanName))
                {
                    sql += " AND u.UserName LIKE @SalesmanName";
                    parameters.Add("@SalesmanName", $"%{request.SalesmanName.Trim()}%");
                }

                if (!string.IsNullOrWhiteSpace(request.MaterialName))
                {
                    sql += " AND dni.Material_name LIKE @MaterialName";
                    parameters.Add("@MaterialName", $"%{request.MaterialName.Trim()}%");
                }

                if (request.StartDate.HasValue)
                {
                    sql += " AND dn.Bill_date >= @StartDate";
                    parameters.Add("@StartDate", request.StartDate.Value.Date);
                }

                if (request.EndDate.HasValue)
                {
                    sql += " AND dn.Bill_date <= @EndDate";
                    parameters.Add("@EndDate", request.EndDate.Value.Date.AddDays(1).AddSeconds(-1));
                }

                if (request.Status.HasValue)
                {
                    sql += " AND dn.Status = @Status";
                    parameters.Add("@Status", request.Status.Value);
                }

                // 添加排序
                sql += " ORDER BY dn.Bill_date DESC, dn.Id DESC";

                _logger.LogInformation("Executing export query with conditions: {@Request}", request);
                _logger.LogInformation("SQL Query: {Sql}", sql);
                
                // 使用带超时的查询
                var deliveryNotices = await connection.QueryAsync<ExportDeliveryNoticeDto>(
                    new CommandDefinition(sql, parameters, commandTimeout: commandTimeout, cancellationToken: cancellationToken)
                );
                
                var recordCount = deliveryNotices.Count();
                _logger.LogInformation("Query completed successfully, retrieved {Count} records", recordCount);
                
                if (recordCount == 0)
                {
                    _logger.LogWarning("No delivery notices found for export with the given conditions");
                }

                // 验证数据完整性
                var validationResult = DataValidationHelper.ValidateDeliveryNoticeData(deliveryNotices, _logger);
                if (!validationResult.IsValid)
                {
                    _logger.LogWarning("数据验证发现问题: {ErrorCount} 个错误", validationResult.Errors.Count);
                    foreach (var error in validationResult.Errors.Take(10)) // 只显示前10个错误
                    {
                        _logger.LogWarning("数据验证错误: {Error}", error);
                    }
                }
                else
                {
                    _logger.LogInformation("数据验证通过，所有记录都有效");
                }

                // 生成Excel文件
                var workbook = new XSSFWorkbook();
                var worksheet = workbook.CreateSheet("发货通知单");

                // 设置表头
                var headerStyle = workbook.CreateCellStyle();
                var headerFont = workbook.CreateFont();
                headerFont.IsBold = true;
                headerStyle.SetFont(headerFont);

                // 创建表头
                var headerRow = worksheet.CreateRow(0);
                var headers = new[]
                {
                    "单据编码", "销售订单号", "客户名称", "销售员姓名", "仓库名称", 
                    "收货人", "收货人电话", "收货人地址", "业务类型", "单据状态",
                    "物料编码", "物料名称", "发货数量", "出库数量", "包装规格",
                    "件数", "单据日期", "交付日期", "批次编号", "备注信息"
                };

                for (int i = 0; i < headers.Length; i++)
                {
                    var cell = headerRow.CreateCell(i);
                    cell.SetCellValue(headers[i]);
                    cell.CellStyle = headerStyle;
                }

                // 填充数据
                int rowIndex = 1;
                foreach (var notice in deliveryNotices)
                {
                    var row = worksheet.CreateRow(rowIndex);
                    
                    _logger.LogDebug("Processing row {RowIndex}: BillCode={BillCode}, SalesOrderCode={SalesOrderCode}, CustomerName={CustomerName}, SalesmanName={SalesmanName}, WarehouseName={WarehouseName}, Receiver={Receiver}, Phone={Phone}, Address={Address}, BizType={BizType}, StatusDescription={StatusDescription}, MaterialCode={MaterialCode}, MaterialName={MaterialName}, DeliveryQuantity={DeliveryQuantity}, OutboundQuantity={OutboundQuantity}, PackagingSpecification={PackagingSpecification}, PieceCount={PieceCount}, BillDate={BillDate}, DeliveryDate={DeliveryDate}, BatchNo={BatchNo}, Remark={Remark}", 
                        rowIndex, notice.BillCode, notice.SalesOrderCode, notice.CustomerName, notice.SalesmanName, notice.WarehouseName, notice.Receiver, notice.Phone, notice.Address, notice.BizType, notice.StatusDescription, notice.MaterialCode, notice.MaterialName, notice.DeliveryQuantity, notice.OutboundQuantity, notice.PackagingSpecification, notice.PieceCount, notice.BillDate, notice.DeliveryDate, notice.BatchNo, notice.Remark);
                    
                    // 单据编码
                    row.CreateCell(0).SetCellValue(notice.BillCode ?? "");
                    
                    // 销售订单号
                    row.CreateCell(1).SetCellValue(notice.SalesOrderCode ?? "");
                    
                    // 客户名称
                    row.CreateCell(2).SetCellValue(notice.CustomerName ?? "");
                    
                    // 销售员姓名
                    row.CreateCell(3).SetCellValue(notice.SalesmanName ?? "");
                    
                    // 仓库名称
                    row.CreateCell(4).SetCellValue(notice.WarehouseName ?? "");
                    
                    // 收货人
                    row.CreateCell(5).SetCellValue(notice.Receiver ?? "");
                    
                    // 收货人电话
                    row.CreateCell(6).SetCellValue(notice.Phone ?? "");
                    
                    // 收货人地址
                    row.CreateCell(7).SetCellValue(notice.Address ?? "");
                    
                    // 业务类型
                    row.CreateCell(8).SetCellValue(notice.BizType ?? "");
                    
                    // 单据状态
                    row.CreateCell(9).SetCellValue(notice.StatusDescription ?? "");
                    
                    // 物料编码
                    row.CreateCell(10).SetCellValue(notice.MaterialCode ?? "");
                    
                    // 物料名称
                    row.CreateCell(11).SetCellValue(notice.MaterialName ?? "");
                    
                    // 发货数量
                    if (notice.DeliveryQuantity.HasValue && notice.DeliveryQuantity.Value > 0)
                    {
                        row.CreateCell(12).SetCellValue(notice.DeliveryQuantity.Value.ToString("F4"));
                    }
                    else
                    {
                        row.CreateCell(12).SetCellValue("0.0000");
                    }
                    
                    // 出库数量
                    if (notice.OutboundQuantity.HasValue && notice.OutboundQuantity.Value > 0)
                    {
                        row.CreateCell(13).SetCellValue(notice.OutboundQuantity.Value.ToString("F4"));
                    }
                    else
                    {
                        row.CreateCell(13).SetCellValue("0.0000");
                    }
                    
                    // 包装规格
                    row.CreateCell(14).SetCellValue(notice.PackagingSpecification ?? "");
                    
                    // 件数
                    if (notice.PieceCount.HasValue && notice.PieceCount.Value > 0)
                    {
                        row.CreateCell(15).SetCellValue(notice.PieceCount.Value.ToString("F0"));
                    }
                    else
                    {
                        row.CreateCell(15).SetCellValue("0");
                    }
                    
                    // 单据日期
                    if (notice.BillDate != default(DateTime) && notice.BillDate.Year > 1900)
                    {
                        row.CreateCell(16).SetCellValue(notice.BillDate.ToString("yyyy-MM-dd"));
                    }
                    else
                    {
                        row.CreateCell(16).SetCellValue("");
                    }
                    
                    // 交付日期
                    if (notice.DeliveryDate != default(DateTime) && notice.DeliveryDate.Year > 1900)
                    {
                        row.CreateCell(17).SetCellValue(notice.DeliveryDate.ToString("yyyy-MM-dd"));
                    }
                    else
                    {
                        row.CreateCell(17).SetCellValue("");
                    }
                    
                    // 批次编号
                    row.CreateCell(18).SetCellValue(notice.BatchNo ?? "");
                    
                    // 备注信息
                    row.CreateCell(19).SetCellValue(notice.Remark ?? "");
                    
                    rowIndex++;
                }

                // 自动调整列宽
                for (int i = 0; i < headers.Length; i++)
                {
                    worksheet.AutoSizeColumn(i);
                }

                _logger.LogInformation("Excel file generated successfully with {RowCount} data rows", rowIndex - 1);

                // 返回Excel文件的字节数组
                using var ms = new MemoryStream();
                workbook.Write(ms);
                var result = ms.ToArray();
                
                _logger.LogInformation("Export completed successfully, file size: {FileSize} bytes", result.Length);
                return result;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error occurred during export operation: {Message}", ex.Message);
                throw;
            }
            finally
            {
                // 确保连接被正确关闭
                if (connection != null)
                {
                    try
                    {
                        if (connection.State == ConnectionState.Open)
                        {
                            connection.Close();
                            _logger.LogInformation("Database connection closed successfully");
                        }
                    }
                    catch (Exception ex)
                    {
                        _logger.LogWarning(ex, "Error occurred while closing database connection: {Message}", ex.Message);
                    }
                    finally
                    {
                        connection.Dispose();
                    }
                }
            }
        }
    }
} 